package cn.dianhun.om.jpa.repository;

import cn.dianhun.om.jpa.model.User;
import cn.dianhun.om.jpa.model.UserDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.stream.Stream;

/**
 * @author cyt
 * @describe
 * @create 2021-01-06 15:17
 */
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    /**
     * 利用JQPl动态查询用户信息
     *
     * @param name
     * @param email
     * @return UserSimpleDto接口
     */
    @Query("select u.name as name,u.email as email from User u where (:name is null or u.name =:name) and (:email is null or u.email =:email)")
    UserOnlyName findByUser(@Param("name") String name, @Param("email") String email);

    /**
     * 利用原始sql动态查询用户信息
     *
     * @param user
     * @return
     */
    @Query(value = "select u.name as name,u.email as email from user u where (:#{#user.name} is null or u.name =:#{#user.name}) and (:#{#user.email} is null or u.email =:#{#user.email})", nativeQuery = true)
    UserOnlyName findByUser(@Param("user") User user);

    /**
     * 测试返回指定的DTO
     *
     * @param id
     * @return
     */
    @Query("select new cn.dianhun.om.jpa.model.UserDto(CONCAT(u.name,'JK123'),u.email,e.idCard) from User u, UserExtend e where u.id= e.userId and u.id=:id")
    UserDto findByUserDtoId(@Param("id") Long id);

    /**
     * 返回四条数据
     *
     * @param name
     * @return
     */
    List<User> findTop4ByName(@Param("name") String name);

    /**
     * 得到名称排序的第一条数据
     *
     * @return
     */
    User findFirstByOrderByNameAsc();

    /**
     * 按照名称查询并且分页
     *
     * @param name
     * @param pageable
     * @return
     */
    @Query(value = "select u from User u where u.name = ?1")
    Page<User> findByName(String name, Pageable pageable);

    /**
     * 按照名称查询并且排序
     *
     * @param name
     * @param sort
     * @return
     */
    @Query("select u from User u where u.name like ?1%")
    List<User> findByName(String name, Sort sort);


    /**
     * 按照名称查找
     *
     * @param name
     * @return
     */
    @Query("from User  where  name =:name")
    User findByQuery(@Param("name") String name);

    /**
     * 查找全部并排序
     *
     * @param sort
     * @return
     */
    List<User> findAll(Sort sort);

    /**
     * 自定义一个查询方法，返回Stream对象，并且有分页属性
     *
     * @param pageable
     * @return
     */
    @Query("select u from User u")
    Stream<User> findAllByCustomQueryAndStream(Pageable pageable);

    /**
     * 测试Slice的返回结果
     *
     * @param pageable
     * @return
     */
    @Query("select u from User u")
    Slice<User> findAllByCustomQueryAndSlice(Pageable pageable);

    /**
     * 按照email查找
     *
     * @param email
     * @return
     */
    User findByEmail(String email);

    /**
     * 按照名称查找
     *
     * @param name
     * @return
     */
    List<User> findByName(String name);

    /**
     * 按照邮箱和名称查找
     *
     * @param email
     * @param name
     * @return
     */
    List<User> findByEmailAndName(String email, String name);
}
